package com.example.exam.staff.dao;

import com.example.exam.db.BaseDao;
import com.example.exam.db.PageInfoBean;
import com.example.exam.staff.model.StaffBean;
import com.example.exam.staff.model.StaffUserBean;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class StaffDao extends BaseDao<StaffBean,Integer> {
    @Override
        /**
            * @Description:添加员工信息SQL语句
            * @Param: []
            * @return: java.lang.String
            * @Author: xhw
            * @Date: 2021/7/26
            */
    public String getAddSql(){
        return "INSERT INTO staff_info(NAME,age,work_year,ability,post,grade) VALUES(:name,:age,:workYear,:ability,:post,:grade)";
    }
    @Override
        /**
            * @Description:删除员工信息SQL语句
            * @Param: []
            * @return: java.lang.String
            * @Author: xhw
            * @Date: 2021/7/26
            */
    public String getDeleteByIdSql(){
        return "DELETE FROM staff_info WHERE id=?";
    }
    @Override
        /**
            * @Description:查询一条员工信息的SQL语句
            * @Param: []
            * @return: java.lang.String
            * @Author: xhw
            * @Date: 2021/7/26
            */
    public String findByIdSql() {
        return "SELECT user_id as user FROM staff_user WHERE staff_id=?";
    }
    /**
     * @Description:根据用户id查询出staff_user表zhong
     * @Param: [id]
     * @return: java.lang.Integer
     * @Author: xhw
     * @Date: 2021/7/26
     */
    public Integer findStaffId(Integer id){

        String sql="SELECT staff_id FROM staff_user WHERE user_id=:id";
        List<Object> list=new ArrayList<Object>();
        list.add(id);
        try{
            return getJtl().queryForObject(sql,new BeanPropertyRowMapper<StaffUserBean>(StaffUserBean.class),list.toArray()).getStaffId();
        }catch(Exception e){
            return 0;
        }
    }
    /**
     * @Description:删除staff_user对应id的数据
     * @Param: [id]
     * @return: java.lang.Integer
     * @Author: xhw
     * @Date: 2021/7/26
     */
    public Integer deleteStaffUser(Integer id){
        String sql="DELETE FROM staff_user WHERE id=？";
        return getJtl().update(sql,id);
    }
    /**
     * @Description:查询一条员工信息中的数据
     * @Param: [id]
     * @return: com.example.exam.staff.model.StaffBean
     * @Author: xhw
     * @Date: 2021/7/26
     */
    public StaffBean selectOne(Integer id){
        try{
            String sql="SELECT * FROM staff_info WHERE id=?";
            return getJtl().queryForObject(sql,new BeanPropertyRowMapper<>(StaffBean .class),id);
        }catch(Exception e){
            return null;
        }
    }
    @Override
        /**
            * @Description:修改员工表中的数据
            * @Param: []
            * @return: java.lang.String
            * @Author: xhw
            * @Date: 2021/7/26
            */
    public String getUpdateAllFieldSql(){
        return "UPDATE staff_info SET NAME=:name,age=:age,work_year=:workYear,ability=:ability,post=:post,grade=:grade WHERE id=:id";
    }
    /**
     * @Description:查询员工信息页面的数据
     * @Param: [pageInfoBean, name]
     * @return: com.example.exam.db.PageInfoBean
     * @Author: xhw
     * @Date: 2021/7/26
     */
    public PageInfoBean list(PageInfoBean pageInfoBean,String name){
        try{
            String sql="SELECT (SELECT login_name FROM sys_user WHERE id=(SELECT user_id FROM staff_user WHERE staff_id=si.id)) AS username,(SELECT NAME FROM staff_post sp WHERE sp.id=si.post) AS pname,si.* FROM staff_info AS si";
            if (StringUtils.isNotEmpty(name)){
                sql += " WHERE NAME LIKE '%"+name+"%'";
            }
            Map<String, Object> paramMap = new HashMap<String, Object>();
            return pageForBean(sql,paramMap,pageInfoBean);
        }catch (Exception e){
            e.fillInStackTrace();
            return null;
        }
    }/**
     * @Description:添加staff_user表中的数据
     * @Param: [sid, uid]
     * @return: java.lang.Integer
     * @Author: xhw
     * @Date: 2021/7/26
     */
    public Integer addStaffUser(Integer sid,Integer uid){
        String sql="INSERT INTO staff_user(staff_id,user_id) VALUES(?,?)";
        List<Object> list=new ArrayList<Object>();
        list.add(sid);
        list.add(uid);
        return getJtl().update(sql,list.toArray());
    }
    /**
     * @Description:根据员工姓名查询员工信息
     * @Param: [name]
     * @return: com.example.exam.staff.model.StaffBean
     * @Author: xhw
     * @Date: 2021/7/26
     */
    public StaffBean findByName(String name){
        String sql="SELECT * FROM staff_info WHERE `name`=?";
        List<Object> list=new ArrayList<Object>();
        list.add(name);
        try{
            return getJtl().queryForObject(sql,new BeanPropertyRowMapper<>(StaffBean.class),name);
        }catch(Exception e){
            return null;
        }
    }
}
